/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.epam.expertsys.dbconn;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.io.IOException;
import org.h2.jdbcx.JdbcConnectionPool;
import java.util.ArrayList;
import com.epam.expertsys.blogic.*;
import org.apache.log4j.Logger;

/**
 *
 * @author Admin
 */
public class AmbulatoryRecordDAOH2 extends GenericDAOH2 implements AmbulatoryRecordDAOIface {

    private static Logger log = Logger.getLogger(AmbulatoryRecordDAOH2.class);
    //TODO: transfer hard-coded table constants to properties
    private static final String AMB_REC_TABLE = "Ambulatory_Record";
    private static final String REC_ID_COL = "Record_id";
    private static final String DOC_ID_COL = "Doctor_id";
    private static final String REQ_ID_COL = "Request_id";
    private static final String TEMPERATURE_COL = "Temperature_C";
    private static final String PRESS_LO_COL = "Blood_pressure_lo";
    private static final String PRESS_HI_COL = "Blood_pressure_hi";
    private static final String DESCR_COL = "Description";
    private static final String RECCOM_COL = "Recomendations";

    /**
     * Standard constructor for this DAO.
     * @param max_conn_count max connection count in connection pool
     */
    public AmbulatoryRecordDAOH2(int maxConnCount) throws SQLException {
        super(maxConnCount);
    }

    @Override
    public void insertAmbRecord(AmbulatoryRecord newAmbRec) throws SQLException {
        Connection conn = null;
        PreparedStatement prepStat = null;

        try {
            conn = conPool.getConnection();
            prepStat = conn.prepareStatement(
                    "INSERT INTO " + AMB_REC_TABLE + " ("
                    + DOC_ID_COL + ","
                    + REQ_ID_COL + ","
                    + TEMPERATURE_COL + ","
                    + PRESS_LO_COL + ","
                    + PRESS_HI_COL + ","
                    + DESCR_COL + ","
                    + RECCOM_COL + ","
                    + ") VALUES (?, ?, ?, ?, ?, ?, ?);");
            fillAmbRecStatement(newAmbRec, prepStat);

            prepStat.executeUpdate();

        } catch (SQLException e) {
            throw new SQLException("SQLException trying insert ambRecord", e);
        } finally {
            closeStatement(prepStat);
            closeConnection(conn);

        }
    }

    private void fillAmbRecStatement(AmbulatoryRecord newAmbRec, PreparedStatement prepStat) throws SQLException {
        if (newAmbRec.getDoctorId() != null) {
            prepStat.setLong(1, newAmbRec.getDoctorId());
        } else {
            throw new SQLException("Doctor ID cannot be NULL");
        }
        if (newAmbRec.getRequestId() != null) {
            prepStat.setLong(2, newAmbRec.getRequestId());
        } else {
            throw new SQLException("Request ID cannot be NULL");
        }
        if (newAmbRec.getTemperature() != null) {
            prepStat.setFloat(3, newAmbRec.getTemperature());
        } else {
            prepStat.setNull(3, java.sql.Types.DOUBLE);
        }
        if (newAmbRec.getPressureLo() != null) {
            prepStat.setLong(4, newAmbRec.getPressureLo());
        } else {
            prepStat.setNull(4, java.sql.Types.INTEGER);
        }
        if (newAmbRec.getPressureHi() != null) {
            prepStat.setLong(5, newAmbRec.getPressureHi());
        } else {
            prepStat.setNull(5, java.sql.Types.INTEGER);
        }
        if (newAmbRec.getDescription() != null) {
            prepStat.setString(6, newAmbRec.getDescription());
        } else {
            throw new SQLException("Description cannot be NULL");
        }
        if (newAmbRec.getRecomendations() != null) {
            prepStat.setString(7, newAmbRec.getRecomendations());
        } else {
            prepStat.setNull(7, java.sql.Types.VARCHAR);
        }
    }

    @Override
    public ArrayList<AmbulatoryRecord> getAmbRecordByPatient(long id) throws SQLException {
        Connection conn = null;
        PreparedStatement prepStat = null;
        ArrayList<AmbulatoryRecord> getList = new ArrayList<AmbulatoryRecord>();

        //TODO: replace hard-coded fields with prepared ones
        try {
            conn = conPool.getConnection();
            prepStat = conn.prepareStatement("Select a.doctor_id, a.request_id, "
                    + "a.temperature_c, a.blood_pressure_Lo, a.blood_pressure_Hi, "
                    + "a.description, a.recomendations "
                    + "from ambulatory_record a, request b "
                    + "where (a.request_id = b.request_id) and "
                    + "(b.patient_id = ?)");

            prepStat.setLong(1, id);
            ResultSet resultSet = prepStat.executeQuery();

            while (resultSet.next()) {
                Long doctor_id = resultSet.getLong(DOC_ID_COL);
                if (resultSet.wasNull()) {
                    doctor_id = null;
                }

                Long request_id = resultSet.getLong(REQ_ID_COL);
                if (resultSet.wasNull()) {
                    request_id = null;
                }

                Float temperature = resultSet.getFloat(TEMPERATURE_COL);
                if (resultSet.wasNull()) {
                    temperature = null;
                }

                Integer pressureLo = resultSet.getInt(PRESS_LO_COL);
                if (resultSet.wasNull()) {
                    pressureLo = null;
                }

                Integer pressureHi = resultSet.getInt(PRESS_HI_COL);
                if (resultSet.wasNull()) {
                    pressureHi = null;
                }

                String description = resultSet.getString(DESCR_COL);
                if (resultSet.wasNull()) {
                    description = null;
                }

                String reccomendations = resultSet.getString(RECCOM_COL);
                if (resultSet.wasNull()) {
                    reccomendations = null;
                }

                getList.add(new AmbulatoryRecord(doctor_id, request_id, temperature,
                        pressureLo, pressureHi, description, reccomendations));
            }



        } catch (SQLException e) {
            //log.error("SQL error");
            throw new SQLException("SQLException trying getPatientById", e);
        } finally {
            closeStatement(prepStat);
            closeConnection(conn);
        }

        return getList;
    }
}
